﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace IDAdmin.Lib.DataLayer
{
    public class LeveStatsWebDB
    {
        List<ServerDTO> gameServerList;//= new List<ServerDTO>() { new ServerDTO() { Name = "vn03", StartSeverDate = "2015-03-12 00:00:00" }, new ServerDTO() { Name = "vn04", StartSeverDate = "2015-04-14 00:00:00" } };
        public LeveStatsWebDB()
        {
            gameServerList = WebDB_N.ListServer().Select(s => new ServerDTO() { Name = s.tablezone, StartSeverDate = s.created.ToString("yyyy-MM-dd HH:mm:ss") }).ToList();
        }
        public Dictionary<string, List<LevelAndPeopleNum>> getLevelStats(DateTime date)
        {

            Dictionary<string, List<LevelAndPeopleNum>> result = new Dictionary<string, List<LevelAndPeopleNum>>();
            try
            {
                foreach (var server in gameServerList)
                {
                    using (SqlConnection conn = ConnectionHelper.GetConnection())
                    {
                        SqlCommand command = new SqlCommand();
                        command.CommandTimeout = 3600;
                        command.CommandType = CommandType.Text;
                        command.Connection = conn;
                        string commandText = "select COUNT(charid) as amount,TB.lv as level from (select charid,max(level) as lv from Level{0} WHERE [datetime] between '{1}' and '{2}' group by charid) as tb group by tb.lv";

                        string commandText2 = string.Format(commandText, server.Name, server.StartSeverDate, date.ToString("yyyy-MM-dd") + " 23:59:59");
                        command.CommandText = commandText2;
                        try
                        {
                            SqlDataReader dataReader = command.ExecuteReader();
                            List<LevelAndPeopleNum> list = new List<LevelAndPeopleNum>();
                            while (dataReader.Read())
                            {
                                LevelAndPeopleNum lap = new LevelAndPeopleNum();
                                lap.Level = dataReader.GetInt64(1).ToString();
                                lap.PeopleNum = dataReader.GetValue(0).ToString();
                                list.Add(lap);
                            }
                            dataReader.Close();
                            if (list != null && list.Count > 0)
                            {
                                result.Add(server.Name, list);
                            }
                        }
                        catch (Exception ex)
                        {

                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public void insertLevelDis(List<LevelStatisticsData> data, DateTime date)
        {
            using (SqlConnection conn = ConnectionHelper.GetConnection())
            {//sp_Insert_PremiumLogin
                SqlCommand deleteCommand = new SqlCommand("spTransfer_delete_LevelStats", conn);
                deleteCommand.CommandTimeout = 3600;
                deleteCommand.CommandType = CommandType.StoredProcedure;
                deleteCommand.Parameters.AddWithValue("@fromDT", date.ToString("yyyy-MM-dd") + " 00:00:00");
                deleteCommand.Parameters.AddWithValue("@toDT", date.ToString("yyyy-MM-dd") + " 23:59:59");
                deleteCommand.ExecuteNonQuery();
            }
            foreach (LevelStatisticsData item in data)
            {
                try
                {
                    using (SqlConnection conn = ConnectionHelper.GetConnection())
                    {
                        SqlCommand command = new SqlCommand("sp_Insert_LevelDistribution", conn);
                        command.CommandTimeout = 3600;
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@DateTime", item.DateTime);
                        command.Parameters.AddWithValue("@Level", item.Level);
                        command.Parameters.AddWithValue("@PeopleNum", item.PeopleNum);
                        command.Parameters.AddWithValue("@PeopleNumPercent", item.PeopleNumPercent);
                        command.ExecuteNonQuery();
                    }
                }
                catch(Exception ex)
                {

                }
                foreach (var item2 in item.PeopleNumAndPercentServerList)
                {
                    try
                    {
                        using (SqlConnection conn = ConnectionHelper.GetConnection())
                        {
                            SqlCommand sqlcommand = new SqlCommand("sp_Insert_ServerLevelDistribution", conn);
                            sqlcommand.CommandTimeout = 3600;
                            sqlcommand.CommandType = CommandType.StoredProcedure;
                            sqlcommand.Parameters.AddWithValue("@DateTime", item2.DateTime);
                            sqlcommand.Parameters.AddWithValue("@Level", item2.Level);
                            sqlcommand.Parameters.AddWithValue("@PeopleNum", item2.PeopleNum);
                            sqlcommand.Parameters.AddWithValue("@PeopleNumPercent", item2.PeopleNumPercent);
                            sqlcommand.Parameters.AddWithValue("@Server", item2.Server);
                            sqlcommand.ExecuteNonQuery();
                        }
                    }
                    catch(Exception ex)
                    {

                    }
                }
            }
        }
        public List<LevelStatisticsData> getLevelDisData(string from, string to, out List<string> serverList, out int peopleNumAll)
        {
            List<LevelStatisticsData> data = new List<LevelStatisticsData>();
            List<PeopleNumAndPercentServer> data2 = new List<PeopleNumAndPercentServer>();
            peopleNumAll = 0;

            using (SqlConnection conn = ConnectionHelper.GetConnection())
            {
                SqlCommand command = new SqlCommand("sp_Select_LevelDis", conn);
                command.CommandTimeout = 3600;
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@fromDT", from);
                command.Parameters.AddWithValue("@toDT", to);
                SqlDataReader dataReader = command.ExecuteReader();
                while (dataReader.Read())
                {
                    LevelStatisticsData lsd = new LevelStatisticsData();
                    lsd.Date = dataReader.GetDateTime(0).ToString("dd/MM/yyyy");
                    lsd.Level = dataReader.GetInt64(1).ToString();
                    lsd.PeopleNum = dataReader.GetInt64(2).ToString();
                    peopleNumAll += int.Parse(lsd.PeopleNum);
                    lsd.PeopleNumPercent = dataReader.GetString(3);
                    data.Add(lsd);
                }
                dataReader.Close();
            }

            using (SqlConnection conn = ConnectionHelper.GetConnection())
            {
                SqlCommand sqlcommand = new SqlCommand("sp_Select_ServerLevelDis", conn);
                sqlcommand.CommandTimeout = 3600;
                sqlcommand.CommandType = CommandType.StoredProcedure;
                sqlcommand.Parameters.AddWithValue("@fromDT", from);
                sqlcommand.Parameters.AddWithValue("@toDT", to);
                SqlDataReader sqldataReader = sqlcommand.ExecuteReader();
                while (sqldataReader.Read())
                {
                    PeopleNumAndPercentServer paps = new PeopleNumAndPercentServer();
                    paps.Date = sqldataReader.GetDateTime(0).ToString("dd/MM/yyyy");
                    paps.Level = sqldataReader.GetInt64(1).ToString();
                    paps.PeopleNum = sqldataReader.GetInt64(2).ToString();
                    paps.PeopleNumPercent = sqldataReader.GetString(3);
                    paps.Server = sqldataReader.GetString(4);
                    data2.Add(paps);
                }
                sqldataReader.Close();
            }
            List<string> levelList = data.Select(s => s.Level).ToList();
            serverList = data2.Select(s => s.Server).Distinct().ToList();
            foreach (var level in levelList)
            {
                LevelStatisticsData lsd = data.Where(w => w.Level.Equals(level)).FirstOrDefault();
                List<PeopleNumAndPercentServer> datatemp = data2.Where(w => w.Level.Equals(level)).ToList();
                foreach (var item3 in datatemp)
                {
                    lsd.PeopleNumAndPercentServerList.Add(item3);
                }
            }
            return data;
        }


    }
}
